package com.ph.irp.base.salesprice.dao.impl;
    
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.salesprice.dao.PBaseSalespriceDao;
import com.ph.irp.model.PBaseSalesprice;
import com.ph.util.DBUtil;
import com.ph.util.DateUtil;

public class PBaseSalespriceDaoImpl implements PBaseSalespriceDao {

	private ComboPooledDataSource dataSource;
	
	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addSalesprice(PBaseSalesprice salespriceModel) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("insert into p_base_salesprice");
			sBuilder.append("(SalesPricePolicyID,MaterialID,CustomerID,SalesNumberFrom,SalesNumberTo,Bid,AuditState,BottomBidControl,BottomBid,ValidDate,InvalidDate)");
			sBuilder.append(" values (");
			sBuilder.append(salespriceModel.getSalespricepolicyId());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getMaterialId());
			sBuilder.append(",");
			sBuilder.append(+ salespriceModel.getCustomerId());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getSalesNumberFrom());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getSalesNumberTo());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getBid());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getAuditState());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getBottomBidControl());
			sBuilder.append(",");
			sBuilder.append(salespriceModel.getBottomBid());
			sBuilder.append(",");
			sBuilder.append("'"+salespriceModel.getValidDate()+"'");
			sBuilder.append(",");
			sBuilder.append("'"+salespriceModel.getInvalidDate()+"'");
			sBuilder.append(")");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sBuilder.toString());
			System.out.println(salespriceModel.getMaterialId() + "--"
					+ salespriceModel.getCustomerId() + "--"
					+ salespriceModel.getSalesNumberFrom() + "--"
					+ salespriceModel.getSalesNumberTo() + "--"
					+ salespriceModel.getBid() + "--"
					+ salespriceModel.getAuditState()+"--"
					+ salespriceModel.getBottomBidControl()+"--"
					+ salespriceModel.getBottomBid()+"--"
					+ salespriceModel.getValidDate()+"--"
					+ salespriceModel.getInvalidDate());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void delectSalesprice(Integer salespriceId) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("delete from p_base_salesprice ");
			sb.append("where salespriceID=?");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, salespriceId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public List<PBaseSalesprice> getAllSalespriceList() throws Throwable{
		List<PBaseSalesprice> salespriceList = new ArrayList<PBaseSalesprice>();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_salesprice");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseSalesprice salesprice = new PBaseSalesprice();
				salesprice.setSalesPriceId(rs.getInt("SalesPriceID"));
				salesprice.setSalespricepolicyId(rs.getInt("SalesPricePolicyID"));
				salesprice.setMaterialId(rs.getInt("MaterialID"));
				salesprice.setCustomerId(rs.getInt("CustomerID"));
				salesprice.setSalesNumberFrom(rs.getFloat("SalesNumberFrom"));
				salesprice.setSalesNumberTo(rs.getFloat("SalesNumberTo"));
				salesprice.setBid(rs.getFloat("Bid"));
				salesprice.setAuditState(rs.getInt("AuditState"));
				salesprice.setBottomBidControl(rs.getInt("BottomBidControl"));
				salesprice.setBottomBid(rs.getFloat("BottomBid"));
				salesprice.setValidDate(DateUtil.date2String(rs.getDate("ValidDate"), "yyyy-MM-dd"));
				salesprice.setInvalidDate(DateUtil.date2String(rs.getDate("InvalidDate"), "yyyy-MM-dd"));
				salespriceList.add(salesprice);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespriceList;
	}

	@Override
	public PBaseSalesprice getSalespriceByID(Integer salespriceId) throws Throwable{
		
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseSalesprice salesprice = new PBaseSalesprice();
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_salesprice  where salespriceId=?");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, salespriceId);
			rs = pStmt.executeQuery();
			rs.next();
			salesprice.setSalesPriceId(salespriceId);
			salesprice.setSalespricepolicyId(rs.getInt("SalesPricePolicyID"));
			salesprice.setMaterialId(rs.getInt("MaterialID"));
			salesprice.setCustomerId(rs.getInt("CustomerID"));
			salesprice.setSalesNumberFrom(rs.getFloat("SalesNumberFrom"));
			salesprice.setSalesNumberTo(rs.getFloat("SalesNumberTo"));
			salesprice.setBid(rs.getFloat("Bid"));
			salesprice.setAuditState(rs.getInt("AuditState"));
			salesprice.setBottomBidControl(rs.getInt("BottomBidControl"));
			salesprice.setBottomBid(rs.getFloat("BottomBid"));
			salesprice.setValidDate(DateUtil.date2String(rs.getDate("ValidDate"), "yyyy-MM-dd"));
			salesprice.setInvalidDate(DateUtil.date2String(rs.getDate("InvalidDate"), "yyyy-MM-dd"));
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salesprice;
	}

	@Override
	public List<PBaseSalesprice> getSalespriceByModel(
			PBaseSalesprice salespriceModel) throws Throwable{
		
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		
		List<PBaseSalesprice> salespriceList = new ArrayList<PBaseSalesprice>();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_salesprice  where 1=1 ");
		
		if(salespriceModel.getSalesPriceId()!=0)
			sb.append(" and salespriceId="+salespriceModel.getSalesPriceId());
		if(salespriceModel.getSalespricepolicyId()!=0)
			sb.append(" and SalesPricePolicyID="+salespriceModel.getSalespricepolicyId());
		if(salespriceModel.getMaterialId()!=0)
			sb.append(" and MaterialID="+salespriceModel.getMaterialId());
		if(salespriceModel.getCustomerId()!=0)
			sb.append(" and CustomerID="+salespriceModel.getCustomerId());
		if(salespriceModel.getAuditState() == 0 || salespriceModel.getAuditState() == 1){
			sb.append(" and AuditState="+salespriceModel.getAuditState());
		}
		System.out.println(sb.toString());
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseSalesprice salesprice = new PBaseSalesprice();
				salesprice.setSalesPriceId(rs.getInt("SalesPriceID"));
				salesprice.setSalespricepolicyId(rs.getInt("SalesPricePolicyID"));
				salesprice.setMaterialId(rs.getInt("MaterialID"));
				salesprice.setCustomerId(rs.getInt("CustomerID"));
				salesprice.setSalesNumberFrom(rs.getFloat("SalesNumberFrom"));
				salesprice.setSalesNumberTo(rs.getFloat("SalesNumberTo"));
				salesprice.setBid(rs.getFloat("Bid"));
				salesprice.setAuditState(rs.getInt("AuditState"));
				salesprice.setBottomBidControl(rs.getInt("BottomBidControl"));
				salesprice.setBottomBid(rs.getFloat("BottomBid"));
				salesprice.setValidDate(DateUtil.date2String(rs.getDate("ValidDate"), "yyyy-MM-dd"));
				salesprice.setInvalidDate(DateUtil.date2String(rs.getDate("InvalidDate"), "yyyy-MM-dd"));
				salespriceList.add(salesprice);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespriceList;
	}

	@Override
	public void updateSalesprice(PBaseSalesprice salespriceModel) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			// 修改语句
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_salesprice set MaterialID=");
			sb.append(salespriceModel.getMaterialId()+",CustomerID=");
			sb.append(salespriceModel.getCustomerId()+",SalesNumberFrom=");
			sb.append(salespriceModel.getSalesNumberFrom()+",SalesNumberTo=");
			sb.append(salespriceModel.getSalesNumberTo()+",Bid=");
			sb.append(salespriceModel.getBid()+",AuditState=");
			sb.append(salespriceModel.getAuditState()+",BottomBidControl=");
			sb.append(salespriceModel.getBottomBidControl()+",BottomBid=");
			sb.append(salespriceModel.getBottomBid()+",ValidDate='");
			sb.append(salespriceModel.getValidDate()).append("'");
			sb.append(",InvalidDate='");
			sb.append(salespriceModel.getInvalidDate()).append("'");
			sb.append("where salespriceID=");
			sb.append(salespriceModel.getSalesPriceId());
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

}
